Hi!

I wonder what the threshold in the SQL monitor Buffer cache hit ratio does? For example the alarms that we get have the value around 99. The threshold value is 0, what dose that mean? Is that the same as 100%? The SQL Group want to change this to 93%, can I just change the threshold value to 93?

//Mats A

See the following MP Wiki entry: http://mpwiki.viacode.com/default.aspx?g=posts&t=154060

Summary: "Percentage of pages that were found in the buffer pool without having to incur a read from disk."

Causes: "Zero value indicates memory bottleneck - SQL Server cannot efficiently cache the data pages in memory; reads from disk are relatively high."

Hi,

The buffer cache hit ratio indicates the percentage of pages found in the SQL Server buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.

Small drops in the buffer cache hit ratio over several hours or even a day does not indicate that there is a specific problem. Likely the user was requesting data that had been removed from memory due to recent lack of usage.

The buffer cache hit ratio should be as high as possible as it is undesirable to have the SQL Server reading from disk unless it is necessary.

Please refer to the link article for more details:

http://logicalread.solarwinds.com/sql-server-buffer-hit-cache-ratio/#.VMH2AXkfoiQ

Regards,

Yan Li

So what happens if I change the threshold value from 0 to 1?

//Mats A

Mats,

Verify you are using the latest version of the SQL MP first. My gut says you're likely using an old one.

Simply said the buffer ratio on all SQL instances should be >95% or higher from a health perspective. Anything below that might be considered deteriorating health. Less hit% means more data needs to be read from disk instead of (buffer)memory.

The threshold =0 in this scenario could be considered 'too late'.

<0 would make no sense.

>0 would pretty much always be true.

>95% (or 93% as your SQL group suggests) should provide more relevant alerts for the SQL group.

If I change the threshold value from 0 to 93 then I will alert when it is above 93%?

Mats,

I'm pretty sure you're not using the latest version. (The Management Packs offered through the SCOM library download are not always the latest versions available on internet. Verify that you are using the latest version.)

http://www.microsoft.com/en-us/download/details.aspx?id=10631

As for an answer to your question:

You do not want an alert above 93%, you want alerts when buffer hit ratio goes below 93% (warning) and below let's say 50% (critical).

Obviously this depends on the importance of said database. But in general databases need to be quick as they can be. In case of SQL server low buffer hit ratio% indicates you could really use more RAM in that server.

• Edited by Manni Calavera 12 hours 45 minutes ago

Mats,

I'm pretty sure you're not using the latest version. (The Management Packs offered through the SCOM library download are not always the latest versions available on internet. Verify that you are using the latest version.)

http://www.microsoft.com/en-us/download/details.aspx?id=10631

As for an answer to your question:

You do not want an alert above 93%, you want alerts when buffer hit ratio goes below 93% (warning) and below let's say 50% (critical).

Obviously this depends on the importance of said database. But in general databases need to be quick as they can be. In case of SQL server low buffer hit ratio% indicates you could really use more RAM in that server.

• Edited by Manni Calavera Thursday, March 05, 2015 7:20 PM